﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using IndexWriter = Lucene.Net.Index.IndexWriter;
using Document = Lucene.Net.Documents.Document;
using Field = Lucene.Net.Documents.Field;
using Could.Service;
using Lucene.Net.Index;
using System.Collections;

namespace CatSentenceAnalyzer
{
    class SqlDataBase
    {
        static public string GetConnectionString ="";
        static public string INDEXFIELD = "ANALYZED";
        static public string TEXTFIELD = "TEXTFIELD";
        static public string WLENFIELD = "WORDLEN";
        static public string SUMFIELD = "SUMKEY";
        static public string IDFIELD = "ID";
        static public string USAGECOUT = "UsageCount";
        static Dictionary<int, string> m_dict = null;
        static public MySqlDataReader m_reader;

        static public  Boolean GetDocuments(NPClass np, string query)
        {
            DbServerData dbsd = np.DbS;
            MySqlConnection connection = new MySqlConnection( dbsd.sqlconnection);
            try
            {
                string sql = query;
                sql = sql.Replace("|T|", dbsd.itable);
                sql = sql.Replace("|U|", np.UserID.ToString());
                sql = sql.Replace("|F|", np.FileID.ToString());
                MySqlCommand command = new MySqlCommand(sql, connection);
                connection.Open();
                m_reader = command.ExecuteReader();
                {
                    return m_reader.HasRows;
                }
            }
            catch (SystemException) {
                return false;
            }
        }

        static public Boolean DeleteDocumentID(NPClass np, string table)
        {
            int irow = 0;
            DbServerData dbsd = np.DbS;
            try
            {
                MySqlConnection connection = new MySqlConnection(dbsd.sqlconnection);
                connection.Open();
                string sql = @"DELETE FROM " + table + " WHERE ID=" + np.transdata.TmsID.ToString();
                MySqlCommand command = new MySqlCommand(sql, connection);
                command.CommandType = System.Data.CommandType.Text;
                irow = command.ExecuteNonQuery();
            }
            catch (System.Exception)
            {
                return false;
            }
            return irow > 0;
        }

        static public Boolean UpdateDocument_UsageCount(NPClass np, Tms tms)
        {
            int irow = 0;
            DbServerData dbsd = np.DbS;
            try
            {
                MySqlConnection connection = new MySqlConnection(dbsd.sqlconnection);
                connection.Open();
                string sql = @"UPDATE " + dbsd.tmstable + " SET UsageCount=UsageCount+1, LastUsageDate=Now()  WHERE ID=" + tms.ID;
                MySqlCommand command = new MySqlCommand(sql, connection);
                command.CommandType = System.Data.CommandType.Text;
                irow = command.ExecuteNonQuery();
            }
            catch (System.Exception)
            {
                return false;
            }
            return irow > 0;
        }

        static public Boolean UpdateDocument_TDB(NPClass np, Tdb tdb)
        {
            int irow = 0;
            DbServerData dbsd = np.DbS;
            try
            {
                MySqlConnection connection = new MySqlConnection(dbsd.sqlconnection);
                connection.Open();
                string sql = @"UPDATE " + dbsd.tdbtable + " SET " + np.textField + "='" + SQLEncode(np.transdata.TSentence) + "', "
                    + np.indexField + "='" + SQLEncode(np.transdata.OSentence) + "', TLastTime= NOW()  WHERE ID=" + tdb.ID;

                MySqlCommand command = new MySqlCommand(sql, connection);
                command.CommandType = System.Data.CommandType.Text;
                irow = command.ExecuteNonQuery();
            }
            catch (System.Exception)
            {
                return false;
            }
            return irow > 0;
        }
        static public Boolean UpdateDocument_TMS(NPClass np,  Tms tms)
        {
            int irow=0;
            DbServerData dbsd = np.DbS;
            try
            {
                MySqlConnection connection = new MySqlConnection(dbsd.sqlconnection);
                connection.Open();
                string sql = @"UPDATE " + dbsd.tmstable + " SET " + np.textField + "='" + SQLEncode(np.transdata.TSentence) + "', "
                    + np.indexField + "='" + SQLEncode(np.transdata.OSentence) + "', TLastTime= NOW()  WHERE ID=" + tms.ID;
                MySqlCommand command = new MySqlCommand(sql, connection);
                command.CommandType = System.Data.CommandType.Text;
                irow = command.ExecuteNonQuery();
            }
            catch (System.Exception )
            {
                return false;
            }
            return irow>0;
        }

        static public int AddNewDocument_TDB(NPClass np, string wordlen, string sumkey, string hashkey)
        {
            int newid = 0;
            DbServerData dbsd = np.DbS;
            try
            {
                MySqlConnection connection = new MySqlConnection(dbsd.sqlconnection);
                connection.Open();
                string sql = @"INSERT INTO " + dbsd.tdbtable + " (FileID,TLastTime,userid," + np.indexField + "," + np.textField + ",wordlen, sumkey) VALUES(" +
                    np.FileID.ToString() + ",NOW()," + np.UserID.ToString() + ",'" + SQLEncode(np.transdata.OSentence) + "','" + SQLEncode(np.transdata.TSentence) + "',"+
                    wordlen +","+ sumkey +"')"; 
                sql = sql + ";SELECT @@identity;";
                MySqlCommand command = new MySqlCommand(sql, connection);
                command.CommandType = System.Data.CommandType.Text;
                newid = Convert.ToInt32(command.ExecuteScalar());
            }
            catch (System.Exception)
            {
                return newid;
            }
            return newid;
        }


        static public int AddNewDocument_TMS(NPClass np)
        {
            int newid = 0;
            DbServerData dbsd = np.DbS;
            try
            {
                MySqlConnection connection = new MySqlConnection(dbsd.sqlconnection);
                connection.Open();
                string sql = @"INSERT INTO " + dbsd.tmstable + " (FileID,TLastTime,LastUsageDate,userid," + np.indexField + "," + np.textField + ") VALUES(" +
                    np.FileID.ToString() + ",NOW(),NOW()," + np.UserID.ToString() + ",'" + SQLEncode(np.transdata.OSentence) + "','" + SQLEncode(np.transdata.TSentence) + "')";
                sql = sql + ";SELECT @@identity;";
                MySqlCommand command = new MySqlCommand(sql, connection);
                command.CommandType = System.Data.CommandType.Text;
                newid = Convert.ToInt32( command.ExecuteScalar() );
            }
            catch (System.Exception)
            {
                return newid;
            }
            return newid ;
        }

        static Dictionary<int, string> SQLRef( )
        {
            m_dict = new Dictionary<int, string>();
            m_dict.Add(39, "\\'");
            m_dict.Add(34, "\\\"");
            m_dict.Add(0, "\\\0");
            m_dict.Add(8, "\\\b");
            m_dict.Add(10, "\\\n");
            m_dict.Add(13, "\\\r");
            m_dict.Add(9, "\\\t");
            m_dict.Add(26, "\\z");
            m_dict.Add(92, "\\");
            m_dict.Add(37, "\\%");
            m_dict.Add(95, "\\_");
            return m_dict;
        }

        static string SQLEncode(string str)
        {
            string s = "";
            if (m_dict == null) m_dict = SQLRef();
            char[] chs = str.ToCharArray();
            for (int i = 0; i < chs.Length; i++ )
            {
                if (m_dict.ContainsKey(chs[i]))
                {
                    s += m_dict[chs[i]];
                }else{
                    s += chs[i];
                }
            }
            return s; 
        }
    }
}